package com.cool4j.framework.dao;

import com.cool4j.framework.FrameworkConstant;
import com.cool4j.framework.orm.EntityHelper;
import com.cool4j.framework.util.CollectionUtil;
import com.cool4j.framework.util.MapUtil;
import com.cool4j.framework.util.PropsUtil;
import com.cool4j.framework.util.StringUtil;

import java.util.Collection;
import java.util.Map;
import java.util.Properties;

/**
 * 封装 SQL 语句相关操作
 *
 * @author Frank create on 2016/1/5 8:59
 */
public class SqlHelper {

  /**
   * SQL 属性文件对象
   */
  private static final Properties sqlProps = PropsUtil.loadProps(FrameworkConstant.SQL_PROPS);

  /**
   * 从 SQL 属性文件中获取相应的 SQL 语句
   */
  public static String getSql(String key) {
    String sql;
    if (sqlProps.containsKey(key)) {
      sql = sqlProps.getProperty(key);
    } else {
      throw new RuntimeException("无法在 " + FrameworkConstant.SQL_PROPS + " 文件中获取属性：" + key);
    }
    return sql;
  }

  /**
   * 生成 select 语句
   */
  public static String generateSelectSql(Class<?> entityClass, String condition, String sort) {
    StringBuilder sql = new StringBuilder("SELECT * FROM ").append(getTable(entityClass));
    sql.append(generateWhere(condition));
    sql.append(generateOrder(sort));
    return sql.toString();
  }

  /**
   * 生成 insert 语句
   */
  public static String generateInsertSql(Class<?> entityClass, Collection<String> fieldNames) {
    StringBuilder sql = new StringBuilder("INSERT INTO ").append(getTable(entityClass));
    if (CollectionUtil.isNotEmpty(fieldNames)) {
      int i = 0;
      StringBuilder columns = new StringBuilder(" ");
      StringBuilder values = new StringBuilder(" VALUES ");
      for (String fieldName : fieldNames) {
        String columnName = EntityHelper.getColumnName(entityClass, fieldName);
        if (i == 0) {
          columns.append("(").append(columnName);
          values.append("(?");
        } else {
          columns.append(", ").append(columnName);
          values.append(", ?");
        }
        if (i == fieldNames.size() - 1) {
          columns.append(")");
          values.append(")");
        }
        i++;
      }
      sql.append(columns).append(values);
    }
    return sql.toString();
  }

  /**
   * 生成 delete 语句
   */
  public static String generateDeleteSql(Class<?> entityClass, String condition) {
    StringBuilder sql = new StringBuilder("DELETE FROM ").append(getTable(entityClass));
    sql.append(generateWhere(condition));
    return sql.toString();
  }

  /**
   * 生成 update 语句
   */
  public static String generateUpdateSql(Class<?> entityClass, Map<String, Object> fieldMap, String condition) {
    StringBuilder sql = new StringBuilder("UPDATE ").append(getTable(entityClass));
    if (MapUtil.isNotEmpty(fieldMap)) {
      sql.append(" SET ");
      int i = 0;
      for (Map.Entry<String, Object> fieldEntry : fieldMap.entrySet()) {
        String fieldName = fieldEntry.getKey();
        String columnName = EntityHelper.getColumnName(entityClass, fieldName);
        if (i == 0) {
          sql.append(columnName).append(" = ?");
        } else {
          sql.append(", ").append(columnName).append(" = ?");
        }
        i++;
      }
    }
    sql.append(generateWhere(condition));
    return sql.toString();
  }

  /**
   * 生成 select count(*) 语句
   */
  public static String generateSelectSqlForCount(Class<?> entityClass, String condition) {
    StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM ").append(getTable(entityClass));
    sql.append(generateWhere(condition));
    return sql.toString();
  }

  /**
   * 生成 select 分页语句（数据库类型为：mysql、oracle、mssql）
   */
  public static String generateSelectSqlForPager(int pageNumber, int pageSize, Class<?> entityClass, String condition, String sort) {
    StringBuilder sql = new StringBuilder();
    String table = getTable(entityClass);
    String where = generateWhere(condition);
    String order = generateOrder(sort);
    String dbType = DatabaseHelper.getDatabaseType();
    if (dbType.equalsIgnoreCase("mysql")) {
      int pageStart = (pageNumber - 1) * pageSize;
      appendSqlForMySql(sql, table, where, order, pageStart, pageSize);
    } else if (dbType.equalsIgnoreCase("oracle")) {
      int pageStart = (pageNumber - 1) * pageSize + 1;
      int pageEnd = pageStart + pageSize;
      appendSqlForOracle(sql, table, where, order, pageStart, pageEnd);
    } else if (dbType.equalsIgnoreCase("mssql")) {
      int pageStart = (pageNumber - 1) * pageSize;
      appendSqlForMsSql(sql, table, where, order, pageStart, pageSize);
    }
    return sql.toString();
  }

  private static String getTable(Class<?> entityClass) {
    return EntityHelper.getTableName(entityClass);
  }

  private static String generateWhere(String condition) {
    String where = "";
    if (StringUtil.isNotEmpty(condition)) {
      where += " WHERE " + condition;
    }
    return where;
  }

  private static String generateOrder(String sort) {
    String order = "";
    if (StringUtil.isNotEmpty(sort)) {
      order += " ORDER BY " + sort;
    }
    return order;
  }

  private static void appendSqlForMySql(StringBuilder sql, String table, String where, String order, int pageStart, int pageEnd) {
    // SELECT * FROM 表名 WHERE 条件 ORDER BY 排序 LIMIT 开始位置，结束位置
    sql.append("SELECT * FROM ").append(table);
    sql.append(where);
    sql.append(order);
    sql.append(" LIMIT ").append(pageStart).append(", ").append(pageEnd);
  }

  private static void appendSqlForOracle(StringBuilder sql, String table, String where, String order, int pageStart, int pageEnd) {
    /*
      SELECT a.* FROM (
        SELECT rownum rn, t.* FROM 表名 t WHERE 条件 ORDER BY 排序
      ) a
      WHERE a.rn >= 开始位置 AND a.rn < 结束位置
     */
    sql.append("SELECT a.* FROM (SELECT rownum rn, t.* FROM ").append(table).append(" t");
    sql.append(where);
    sql.append(order);
    sql.append(") a WHERE a.rn >= ").append(pageStart).append(" AND a.rn < ").append(pageEnd);
  }

  private static void appendSqlForMsSql(StringBuilder sql, String table, String where, String order, int pageStart, int pageEnd) {
    /*
      SELECT TOP 结束位置 * FROM 表名 WHERE 条件 AND id NOT IN (
        SELECT TOP 开始位置 id FROM 表名 WHERE 条件 ORDER BY 排序
      ) ORDER BY 排序
     */
    sql.append("SELECT TOP ").append(pageEnd).append(" * FROM ").append(table);
    if (StringUtil.isNotEmpty(where)) {
      sql.append(where).append(" AND ");
    } else {
      sql.append(" WHERE ");
    }
    sql.append("id NOT IN (SELECT TOP ").append(pageStart).append(" id FROM ").append(table);
    sql.append(where);
    sql.append(order);
    sql.append(") ").append(order);
  }

}
